﻿using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Data.EffiProzDB;
using System.Diagnostics;

namespace EffiProzDBTest.ADO.Net
{
    /// <summary>
    /// Summary description for PerfTest
    /// </summary>
    [TestClass]
    public class PerfTest
    {
        string dataPath = "../../../EffiProzDBTest/Data/";

        public PerfTest()
        {
            AppDomain.CurrentDomain.SetData("DataDirectory", dataPath);
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion


        


        [TestMethod]
        public void TestMethod1()
        {
            string connString = @"Data Source=|DataDirectory|\TestSQLDB.sdf;Persist Security Info=False;";
            int insertCount = 100000;
            int searchCount = 100;
            int updateCount = 100;
            int deleteCount = 100;

            using (DbConnection conn = new SqlCeConnection(connString))
            {
                
                conn.Open();
                DbCommand command = conn.CreateCommand();
                command.CommandText = "DROP TABLE TEST;";
                command.ExecuteNonQuery();
                command.CommandText = "CREATE TABLE TEST(ID int, ID2 int, NAME nvarchar(255),CONSTRAINT prim1 PRIMARY KEY (ID));";
                command.ExecuteNonQuery();

                command.CommandText = "INSERT INTO TEST(ID ,ID2, NAME ) VALUES(@ID , @ID2, @Name);";
                command.Prepare();

                DbParameter param1 = command.CreateParameter();
                param1.ParameterName = "@ID";
                DbParameter param2 = command.CreateParameter();
                param2.ParameterName = "@ID2";
                DbParameter param3 = command.CreateParameter();
                param3.ParameterName = "@Name";
                command.Parameters.Add(param1);
                command.Parameters.Add(param2);
                command.Parameters.Add(param3);

                Stopwatch sw = new Stopwatch();
                Random rand = new Random();

                sw.Start();
                for (int i = 0; i < insertCount; i++)
                {
                    param1.Value = i;
                    param2.Value = rand.Next(insertCount);
                    param3.Value = "MyName" + i;
                    command.ExecuteNonQuery();
                }

                sw.Stop();

                Debug.WriteLine("SqlCE Time per Insert (ms) :" + ((double)sw.ElapsedMilliseconds / insertCount));

                command = conn.CreateCommand();
                command.CommandText = "SELECT * FROM TEST WHERE ID=@ID;";
                command.Prepare();

                param1 = command.CreateParameter();
                param1.ParameterName = "@ID";
                command.Parameters.Add(param1);

                sw.Reset(); sw.Start();
                DbDataReader reader;
                for (int i = 0; i < searchCount; i++)
                {
                    param1.Value = rand.Next(insertCount);
                    reader = command.ExecuteReader();
                    while (reader.Read()) ;
                }

                sw.Stop();
                Debug.WriteLine("SqlCE Time per Search(PK) (ms) :" + ((double)sw.ElapsedMilliseconds / searchCount));

                command = conn.CreateCommand();
                command.CommandText = "UPDATE TEST SET NAME=@NAME WHERE ID=@ID;";
                command.Prepare();

                param1 = command.CreateParameter();
                param1.ParameterName = "@ID";
                command.Parameters.Add(param1);
                param2 = command.CreateParameter();
                param2.ParameterName = "@NAME";
                command.Parameters.Add(param2);

                sw.Reset(); sw.Start();
                for (int i = 0; i < updateCount; i++)
                {
                    param1.Value = rand.Next(insertCount);
                    param2.Value = "UPDATED";
                    command.ExecuteNonQuery();
                }

                sw.Stop();
                Debug.WriteLine("SqlCE Time per Update (ms) :" + ((double)sw.ElapsedMilliseconds / searchCount));

                command = conn.CreateCommand();
                command.CommandText = "DELETE  FROM TEST WHERE ID=@ID;";
                command.Prepare();

                param1 = command.CreateParameter();
                param1.ParameterName = "@ID";
                command.Parameters.Add(param1);

                sw.Reset(); sw.Start();
                for (int i = 0; i < deleteCount; i++)
                {
                    param1.Value = rand.Next(insertCount);
                    reader = command.ExecuteReader();
                    while (reader.Read()) ;
                }

                sw.Stop();
                Debug.WriteLine("SqlCE Time per Delete (ms) :" + ((double)sw.ElapsedMilliseconds / searchCount));

            }


            connString = @"Type=File ; Data Source=|DataDirectory|\TestPerfDB; User=sa; Password=;";
            
            using (DbConnection conn = new EffiProzDBConnection(connString))
            {

                conn.Open();
                DbCommand command = conn.CreateCommand();
                command.CommandText = "DROP TABLE TEST IF EXISTS;";
                command.ExecuteNonQuery();
                command.CommandText = "CREATE CACHED TABLE TEST(ID int Primary Key,ID2 int, NAME varchar(255));";
                command.ExecuteNonQuery();

                command.CommandText = "INSERT INTO TEST(ID ,ID2, NAME ) VALUES(@ID ,@ID2, @Name);";
                command.Prepare();

                DbParameter param1 = command.CreateParameter();
                param1.ParameterName = "@ID";
                DbParameter param2 = command.CreateParameter();
                param2.ParameterName = "@ID2";
                DbParameter param3 = command.CreateParameter();
                param3.ParameterName = "@Name";
                command.Parameters.Add(param1);
                command.Parameters.Add(param2);
                command.Parameters.Add(param3);

                Stopwatch sw = new Stopwatch();
                Random rand = new Random();

                sw.Start();
                for (int i = 0; i < insertCount; i++)
                {
                    param1.Value = i;                    
                    param2.Value = rand.Next(insertCount);
                    param3.Value = "MyName" + i;
                    command.ExecuteNonQuery();
                }

                sw.Stop();

                Debug.WriteLine("EffiProz Time per Insert (ms) :" + ((double)sw.ElapsedMilliseconds / insertCount));

                command = conn.CreateCommand();
                command.CommandText = "SELECT * FROM TEST WHERE ID=@ID;";
                command.Prepare();

                param1 = command.CreateParameter();
                param1.ParameterName = "@ID";
                command.Parameters.Add(param1);

                sw.Reset(); sw.Start();
                DbDataReader reader;
                for (int i = 0; i < searchCount; i++)
                {                    
                    param1.Value = rand.Next(insertCount);
                    reader = command.ExecuteReader();
                    while (reader.Read()) ;
                }

                sw.Stop();
                Debug.WriteLine("EffiProz Time per Search(PK) (ms) :" + ((double)sw.ElapsedMilliseconds / searchCount));

                command = conn.CreateCommand();
                command.CommandText = "UPDATE TEST SET NAME=@NAME WHERE ID=@ID;";
                command.Prepare();

                param1 = command.CreateParameter();
                param1.ParameterName = "@ID";
                command.Parameters.Add(param1);
                param2 = command.CreateParameter();
                param2.ParameterName = "@NAME";
                command.Parameters.Add(param2);

                sw.Reset(); sw.Start();
                for (int i = 0; i < updateCount; i++)
                {
                    param1.Value = rand.Next(insertCount);
                    param2.Value = "UPDATED";
                    command.ExecuteNonQuery();                    
                }

                sw.Stop();
                Debug.WriteLine("EffiProz Time per Update(ms) :" + ((double)sw.ElapsedMilliseconds / updateCount));


                command = conn.CreateCommand();
                command.CommandText = "DELETE FROM TEST WHERE ID=@ID;";
                command.Prepare();

                param1 = command.CreateParameter();
                param1.ParameterName = "@ID";
                command.Parameters.Add(param1);

                sw.Reset(); sw.Start();
                for (int i = 0; i < deleteCount; i++)
                {
                    param1.Value = rand.Next(insertCount);
                    reader = command.ExecuteReader();
                    while (reader.Read()) ;
                }

                sw.Stop();
                Debug.WriteLine("EffiProz Time per Delete (ms) :" + ((double)sw.ElapsedMilliseconds / deleteCount));

            
            }


            connString = @"Type=File ; Data Source=|DataDirectory|\TestPerfDB2; User=sa; Password=;";

            using (DbConnection conn = new EffiProzDBConnection(connString))
            {

                conn.Open();
                DbCommand command = conn.CreateCommand();
                command.CommandText = "DROP TABLE TEST IF EXISTS;";
                command.ExecuteNonQuery();
                command.CommandText = "CREATE TABLE TEST(ID int Primary Key,ID2 int, NAME varchar(255));";
                command.ExecuteNonQuery();

                command.CommandText = "INSERT INTO TEST(ID ,ID2, NAME ) VALUES(@ID ,@ID2, @Name);";
                command.Prepare();

                DbParameter param1 = command.CreateParameter();
                param1.ParameterName = "@ID";
                DbParameter param2 = command.CreateParameter();
                param2.ParameterName = "@ID2";
                DbParameter param3 = command.CreateParameter();
                param3.ParameterName = "@Name";
                command.Parameters.Add(param1);
                command.Parameters.Add(param2);
                command.Parameters.Add(param3);

                Stopwatch sw = new Stopwatch();
                Random rand = new Random();

                sw.Start();
                for (int i = 0; i < insertCount; i++)
                {
                    param1.Value = i;
                    param2.Value = rand.Next(insertCount);
                    param3.Value = "MyName" + i;
                    command.ExecuteNonQuery();
                }

                sw.Stop();

                Debug.WriteLine("EffiProz ST Time per Insert (ms) :" + ((double)sw.ElapsedMilliseconds / insertCount));

                command = conn.CreateCommand();
                command.CommandText = "SELECT * FROM TEST WHERE ID=@ID;";
                command.Prepare();

                param1 = command.CreateParameter();
                param1.ParameterName = "@ID";
                command.Parameters.Add(param1);

                sw.Reset(); sw.Start();
                DbDataReader reader;
                for (int i = 0; i < searchCount; i++)
                {
                    param1.Value = rand.Next(insertCount);
                    reader = command.ExecuteReader();
                    while (reader.Read()) ;
                }

                sw.Stop();
                Debug.WriteLine("EffiProz ST Time per Search(PK) (ms) :" + ((double)sw.ElapsedMilliseconds / searchCount));

                command = conn.CreateCommand();
                command.CommandText = "UPDATE TEST SET NAME=@NAME WHERE ID=@ID;";
                command.Prepare();

                param1 = command.CreateParameter();
                param1.ParameterName = "@ID";
                command.Parameters.Add(param1);
                param2 = command.CreateParameter();
                param2.ParameterName = "@NAME";
                command.Parameters.Add(param2);

                sw.Reset(); sw.Start();
                for (int i = 0; i < updateCount; i++)
                {
                    param1.Value = rand.Next(insertCount);
                    param2.Value = "UPDATED";
                    command.ExecuteNonQuery();
                }

                sw.Stop();
                Debug.WriteLine("EffiProz ST Time per Update(ms) :" + ((double)sw.ElapsedMilliseconds / updateCount));


                command = conn.CreateCommand();
                command.CommandText = "DELETE FROM TEST WHERE ID=@ID;";
                command.Prepare();

                param1 = command.CreateParameter();
                param1.ParameterName = "@ID";
                command.Parameters.Add(param1);

                sw.Reset(); sw.Start();
                for (int i = 0; i < deleteCount; i++)
                {
                    param1.Value = rand.Next(insertCount);
                    reader = command.ExecuteReader();
                    while (reader.Read()) ;
                }

                sw.Stop();
                Debug.WriteLine("EffiProz ST Time per Delete (ms) :" + ((double)sw.ElapsedMilliseconds / deleteCount));


            }


        }
    }
}
